package com.AEWMS.Service;

import com.AEWMS.Dao.*;
import com.AEWMS.Pojo.AdminAttendInfoView;
import com.AEWMS.Pojo.StuAttendWarnView;
import com.AEWMS.Pojo.StudentAttendanceStatisticsView;
import com.AEWMS.Utlis.DbCon;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * @author lhs
 * @create 2021/2/22 16:16
 */
public class AttendanceWarningService {

    private Connection conn = null ;
    private PreparedStatement pstmt = null ;

    public AttendanceWarningService() {//改加上姓名之后的输出数据
        DbCon dbcon;
        try {
            dbcon = new DbCon();
            this.conn = dbcon.getConnection();
        } catch (Exception e) {
            System.out.println("AttendanceWarningService 连接数据库失败");
            e.printStackTrace();
        }
    }

    public void closeCon(){
        try {
            this.conn.close();
        } catch (Exception e) {
            System.out.println("数据库连接关闭");
            e.printStackTrace();
        }
    }

    /**
     * 查找所有考勤对象
     * @return 所有考勤对象信息
     */
    public ArrayList<StudentAttendanceStatisticsView> findAllAttendStat(){
        ArrayList<StudentAttendanceStatisticsView> AllAttendStatList = new ArrayList();//升级版数组，返回所有考勤类型对象
        String sql = "SELECT * FROM student_attendance_statistics_view";
        try {
            this.pstmt = conn.prepareStatement(sql);
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()){
                StudentAttendanceStatisticsView s = new StudentAttendanceStatisticsView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setWeek(rs.getString(10));
                s.setStatistics(rs.getString(11));
                AllAttendStatList.add(s);//将查找到的行（每个考勤对象）添加到数组
            }
        } catch (Exception e) {
            System.out.println("AllAttendStatList 失败");
            System.out.println(e);
        }
        return AllAttendStatList;//输出包含所有考勤对象的数组
    }

    /**
     * 查找考勤对象
     * @param id 通过id
     * @return 返回一个考勤对象
     */
    public StudentAttendanceStatisticsView findAttendStatById(String id){
        StudentAttendanceStatisticsView s = new StudentAttendanceStatisticsView();
        String sql = "SELECT * FROM student_attendance_statistics_view WHERE student_number = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);//将sql语句放到数据库的准备对象
            this.pstmt.setString(1,id);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();//执行查询返回结果集
            while (rs.next()){
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setWeek(rs.getString(10));
                s.setStatistics(rs.getString(11));
            }
        }
        catch (Exception e) {
            System.out.println("findAttendStatById 失败");
            System.out.println(e);
        }
        return s;
    }
    /**
     * 学生查找考勤对象
     * @param id 通过id,year,type
     * @return 返回一个考勤对象
     */
    public ArrayList<StudentAttendanceStatisticsView> findAttendStatByIdYearType(String id,String year,String type){
        ArrayList<StudentAttendanceStatisticsView> StuAttendStatList = new ArrayList<>();
        String sql = "SELECT * FROM student_attendance_statistics_view WHERE student_number = ? AND semester_year = ? AND semester_type = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);//将sql语句放到数据库的准备对象
            this.pstmt.setString(1,id);//将传入id值替换问号放在SQL
            this.pstmt.setString(2,year);//将传入id值替换问号放在SQL
            this.pstmt.setString(3,type);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();//执行查询返回结果集
            while (rs.next()){
                StudentAttendanceStatisticsView s = new StudentAttendanceStatisticsView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setWeek(rs.getString(10));
                s.setStatistics(rs.getString(11));
                StuAttendStatList.add(s);
            }
        }
        catch (Exception e) {
            System.out.println("findAttendStatByIdYearType 失败");
            System.out.println(e);
        }
        return StuAttendStatList;
    }
    /**
     * 管理员查找考勤对象
     * @param   year,type
     * @return 返回一个考勤对象
     */
    public ArrayList<AdminAttendInfoView> findAttendStatByYearType(String year, String type){
        ArrayList<AdminAttendInfoView> StuAttendStatList = new ArrayList<>();
        String sql = "SELECT * FROM admin_attend_info_view WHERE semester_year = ? AND semester_type = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);//将sql语句放到数据库的准备对象
            this.pstmt.setString(1,year);//将传入id值替换问号放在SQL
            this.pstmt.setString(2,type);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();//执行查询返回结果集
            while (rs.next()){
                AdminAttendInfoView s = new AdminAttendInfoView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setStatistics(rs.getString(10));
                s.setRank("");
                StuAttendStatList.add(s);
            }
        }
        catch (Exception e) {
            System.out.println("findAttendStatByYearType 失败");
            System.out.println(e);
        }
        return StuAttendStatList;
    }
    /**
     * 添加考勤对象
     * @return 一个考勤对象
     */
    public boolean insertAttend(Attendance s){
        boolean flag = false;
        String sql = "INSERT INTO attendance_info VALUES(?,?,?,?,?,?)";
        try {
            this.pstmt = conn.prepareStatement(sql);//sql语句已经在这个准备对象里，有问号就要用准备对象
            this.pstmt.setString(1,s.getStudentNumber());
            this.pstmt.setString(2,s.getSemester());
            this.pstmt.setString(3,s.getWeek());
            this.pstmt.setString(4,s.getAttendanceTypeId());
            this.pstmt.setFloat(5,s.getAttendanceTimes());
            this.pstmt.setString(6,s.getAttendanceNote());
            int rs = this.pstmt.executeUpdate();//这里不用再传sql
            if ( rs == 1 ) {
                flag = true;
            }
        }
        catch (Exception e){
            System.out.println("insertAttend defeat");
            System.out.println(e);
        }
        return flag;
    }

    /**
     * 修改一个考勤对象
     * @param s 一个考勤对象
     * @return 是否成功
     */
    public boolean updateAttend(Attendance s) {
        boolean flag = false;
        String sql = "UPDATE attendance_info SET attendance_type_id = ?,attendance_times = ?,attendance_note = ? WHERE student_number = ? AND semester = ? AND week = ?";//这里的字段名要与数据库的一致
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,s.getAttendanceTypeId());
            this.pstmt.setFloat(2,s.getAttendanceTimes());
            this.pstmt.setString(3,s.getAttendanceNote());
            this.pstmt.setString(4,s.getStudentNumber());
            this.pstmt.setString(5,s.getSemester());
            this.pstmt.setString(6,s.getWeek());
            int rs = this.pstmt.executeUpdate();
            if ( rs == 1 ) {
                flag = true;
            }
        } catch (Exception e) {
            System.out.println("updateAttend 失败");
            System.out.println(e);
        }
        return flag;

    }

    /**
     * 删除一个考勤对象
     * @param s 考勤对象
     * @return 是否成功
     */
    public boolean deleteAttendById(Attendance s){
        boolean flag = false;
        String sql = "DELETE FROM attendance_info WHERE student_number = ? && attendance_type_id = ?  && semester = ? && week = ?";//这里的字段名要与数据库的一致
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,s.getStudentNumber());
            this.pstmt.setString(2,s.getAttendanceTypeId());
            this.pstmt.setString(3,s.getSemester());
            this.pstmt.setString(4,s.getWeek());
            int rs = this.pstmt.executeUpdate();
            if ( rs == 1 ) {
                flag = true;
            }
        }
        catch (Exception e){
            System.out.println("deleteAttendById 失败");
            System.out.println(e);
        }
        return flag;
    }

}




